#include "db.h"
#include "u.h"
#include <QSqlQuery>
#include <QSqlError>
#include <QVariant>
#include <QDebug>
#include <QDate>
#include <QDateTime>
#include <QSqlRecord>


DB *DB::instance = new DB();

DB *DB::getInstance()
{
    return instance;
}

DB::DB(QObject *parent)
    : QObject{parent}
{
}

bool DB::isTableExists(const QString &tableName)
{
    QSqlQuery query(m_db);
    // 查询sqlite_master系统表，检查是否存在指定表
    // type='table' 表示只检查表，不包括视图等
    // name=? 是参数化查询，避免SQL注入和表名包含特殊字符的问题
    QString sql = "SELECT name FROM sqlite_master WHERE type='table' AND name=?";
    query.prepare(sql);
    query.addBindValue(tableName);

    if (!query.exec()) {
        qWarning() << "查询表是否存在失败：" << query.lastError().text();
        return false;
    }

    // 如果查询结果有记录，说明表存在
    bool r = query.next();
    query.clear();
    return r;
}

bool DB::initDB()
{
    //"qt_sql_default_connection"
    if (QSqlDatabase::contains("qt_sql_default_connection")) {
        m_db = QSqlDatabase::database("qt_sql_default_connection");
    } else {
        m_db = QSqlDatabase::addDatabase("QSQLITE");
        m_db.setDatabaseName("lepure.db");
    }
    if (m_db.isOpen()) {
        return true;
    }
    if (!m_db.open()) {
        qDebug() << __FILE__ << __LINE__ << "Error: Failed to connect database." << m_db.lastError();
        return false;
    } else {
        qDebug() << __FILE__ << __LINE__ << "Succeed to connect database." ;
    }
    return true;
}

QList<QHash<QString, QVariant>> DB::queryUsers(const QString& filter)
{
    QSqlQuery query(m_db);
    QString sql = "SELECT * from user";
    if (filter.length() > 0) sql += " where name like :filter or real_name like :filter or id=:filter";
    query.prepare(sql);
    query.bindValue(":filter", "%" + filter + "%");
    if(!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << "query error: " << query.lastError();
    }
    else {
        qDebug() << __FILE__ << __LINE__ << "query success!";
    }

    QSqlRecord record = query.record();
    int columnCount = record.count();

    QList<QHash<QString, QVariant>> list;
    while (query.next()) {
        QHash<QString, QVariant> hs;
        for (int i=0; i<columnCount; i++){
            hs[record.fieldName(i)]    = query.value(record.fieldName(i)).toString();
        }
        list.push_back(hs);
    }
    record.clear();
    query.clear();
    return list;
}
QList<QHash<QString, QVariant>> DB::queryBatches(const QString& filter)
{
    QSqlQuery query(m_db);
    QString sql = "SELECT * from batch";
    if (filter.length() > 0) sql += " where :filter";
    sql += " order by create_time desc";
    query.prepare(sql);
    query.bindValue(":filter", filter);
    if(!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << "query error: " << query.lastError();
    }
    else {
        qDebug() << __FILE__ << __LINE__ << "query success!";
    }

    QSqlRecord record = query.record();
    int columnCount = record.count();

    QList<QHash<QString, QVariant>> list;
    while (query.next()) {
        QHash<QString, QVariant> hs;
        for (int i=0; i<columnCount; i++){
            hs[record.fieldName(i)]    = query.value(record.fieldName(i)).toString();
        }
        list.push_back(hs);
    }
    record.clear();
    query.clear();
    return list;
}

QList<QHash<QString, QVariant> > DB::queryBatchData(const QString& beginTime, const QString& endTime)
{
    QSqlQuery query(m_db);
    QString sql = "SELECT * from batch_data where create_time>=:begin_time and create_time<=:end_time";
    sql += " order by create_time desc";
    query.prepare(sql);
    query.bindValue(":begin_time", beginTime);
    query.bindValue(":end_time", endTime);
    if(!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << "query error: " << query.lastError();
    }
    else {
        qDebug() << __FILE__ << __LINE__ << "query success!";
    }

    QSqlRecord record = query.record();
    int columnCount = record.count();

    QList<QHash<QString, QVariant>> list;
    while (query.next()) {
        QHash<QString, QVariant> hs;
        for (int i=0; i<columnCount; i++){
            hs[record.fieldName(i)]  = query.value(record.fieldName(i)).toString();
        }
        list.push_back(hs);
    }
    record.clear();
    query.clear();
    return list;
}
QList<QHash<QString, QVariant>> DB::queryOperation(const QString& filter)
{
    QSqlQuery query(m_db);
    QString sql = "SELECT * from log";
    if (filter.length() > 0) sql += " where :filter";
    sql += " order by create_time desc";
    query.prepare(sql);
    query.bindValue(":filter", filter);
    if(!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << "query error: " << query.lastError();
    }
    else {
        qDebug() << __FILE__ << __LINE__ << "query success!";
    }

    QSqlRecord record = query.record();
    int columnCount = record.count();

    QList<QHash<QString, QVariant>> list;
    while (query.next()) {
        QHash<QString, QVariant> hs;
        for (int i=0; i<columnCount; i++){
            hs[record.fieldName(i)]    = query.value(record.fieldName(i)).toString();
        }
        list.push_back(hs);
    }
    record.clear();
    query.clear();
    return list;
}
QList<QHash<QString, QVariant>> DB::queryWarn(const QString& filter)
{
    QSqlQuery query(m_db);
    QString sql = "SELECT * from alarm_log";
    if (filter.length() > 0) sql += " where :filter";
    sql += " order by create_time desc";
    query.prepare(sql);
    query.bindValue(":filter", filter);
    if(!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << "query error: " << query.lastError();
    }
    else {
        qDebug() << __FILE__ << __LINE__ << "query success!";
    }

    QSqlRecord record = query.record();
    int columnCount = record.count();

    QList<QHash<QString, QVariant>> list;
    while (query.next()) {
        QHash<QString, QVariant> hs;
        for (int i=0; i<columnCount; i++){
            hs[record.fieldName(i)]    = query.value(record.fieldName(i)).toString();
        }
        list.push_back(hs);
    }
    record.clear();
    query.clear();
    return list;
}
bool DB::initUser()
{
    if (!isTableExists("user")){
        QSqlQuery query(m_db);
        QString createSql = "CREATE TABLE IF NOT EXISTS user ("
                            "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                            "name TEXT UNIQUE,"
                            "password TEXT,"
                            "real_name TEXT,"
                            "expiration TEXT,"
                            "status INTEGER,"
                            "permission INTEGER,"
                            "create_by TEXT,"
                            "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
                            "update_by TEXT,"
                            "update_time TEXT"
                            ");";
        query.prepare(createSql);
        if(!query.exec()) {
            qDebug() << __FILE__ << __LINE__ << "Error: Fail to create table." << query.lastError();
            return false;
        } else {
            qDebug() << __FILE__ << __LINE__ << "Table created!" << __DATE__;
        }
        query.clear();

        QString insertAdmin = "insert into user(name,password,real_name,expiration,status,permission,create_by,create_time) values('admin','123','超级管理员','2050-12-12',1,0,'系统生成',datetime('now', 'localtime'))";
        if(!query.exec(insertAdmin)) {
            qDebug() << __FILE__ << __LINE__ << "Error: Fail to create table." << query.lastError() << insertAdmin;
            return false;
        } else {
            qDebug() << __FILE__ << __LINE__ << "INSERT super admin" << __DATE__;
        }
        query.clear();
    }else{
        qDebug() << __FILE__<< "user表已经存在" << U::getCompileDate();
    }
    return true;
}

bool DB::initParam()
{
    if (!isTableExists("param")){
        QSqlQuery query(m_db);
        QString createSql = "CREATE TABLE IF NOT EXISTS param ("
                            "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                            "name TEXT UNIQUE,"
                            "value TEXT,"
                            "desc TEXT,"
                            "status INTEGER,"
                            "create_by TEXT,"
                            "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
                            "update_by TEXT,"
                            "update_time TEXT"
                            ");";
        query.prepare(createSql);
        if(!query.exec()) {
            qDebug() << __FILE__ << __LINE__ << "Error: Fail to create table." << query.lastError();
            return false;
        } else {
            qDebug() << __FILE__ << __LINE__ << "Table created!" << __DATE__;
        }
        query.clear();

        insertParamDefault("machine_type", "EPS-MG", "硬件型号");
        insertParamDefault("product_serialNumber", "", "设备序列号");

        insertParamDefault("system_optional", "ph", "设备选配设置");
        insertParamDefault("system_language", "chinese", "语言设置");
        insertParamDefault("system_dataSamplingPeriod","10s", "采样时间设置");
        insertParamDefault("system_weightUnit", "g", "重量单位设置");
        insertParamDefault("system_decimalPlaces", "1", "重量小数位数设置");

        insertParamDefault("system_temperatureUnit", "celsius", "温度单位设置");
        insertParamDefault("system_dateFormat", "yyyy_mm_dd", "日期格式设置");

        insertParamDefault("system_timeFormat", "hh_mm_ss", "时间格式设置");
        insertParamDefault("system_clockType", "local", "时钟设置");
        insertParamDefault("system_signatureType", "one", "电子签名设置");
        insertParamDefault("system_networkType", "wired", "网络通讯设置");

        insertParamDefault("login_passwordComplexity", "simple", "密码复杂度");
        insertParamDefault("login_passwordLength", "6", "密码长度");
        insertParamDefault("login_passwordLockAttempts", "5", "密码尝试次数");
        insertParamDefault("login_lockDuration", "3min", "锁屏锁定时间");
        insertParamDefault("login_autoLogoutWait", "5min", "自动登出等待时间");
    }else{
        qDebug() << __FILE__<< "user表已经存在" << U::getCompileDate();
        return true;
    }
    return true;
}

bool DB::initAlarm()
{
    if (!isTableExists("alarm")){
        QSqlQuery query(m_db);
        QString createSql = "CREATE TABLE IF NOT EXISTS alarm ("
                            "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                            "name TEXT UNIQUE,"
                            "ll TEXT DEFAULT '0.0',"
                            "l TEXT DEFAULT '0.0',"
                            "h TEXT DEFAULT '0.0',"
                            "hh TEXT DEFAULT '0.0',"
                            "llStatus INTEGER  DEFAULT 0,"
                            "lStatus INTEGER  DEFAULT 0,"
                            "hStatus INTEGER  DEFAULT 0,"
                            "hhStatus INTEGER  DEFAULT 0,"
                            "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
                            "create_by TEXT,"
                            "update_by TEXT,"
                            "update_time TEXT"
                            ");";
        query.prepare(createSql);
        if(!query.exec()) {
            qDebug() << __FILE__ << __LINE__ << "Error: Fail to create table." << query.lastError();
            return false;
        } else {
            qDebug() << __FILE__ << __LINE__ << "Table created!" << __DATE__;
        }
        query.clear();

        if (!m_db.transaction()) {
            qCritical() << "开启事务失败：" << m_db.lastError().text();
            return false;
        }
        qDebug()<< "begin:" << U::getThDt();
        QString insert = "insert into alarm(name, create_by, create_time) values('speed','系统生成',datetime('now', 'localtime'))";
        if(!query.exec(insert)) {
            qDebug() << __FILE__ << __LINE__ << "Error: Fail to create table." << query.lastError() << insert;
            m_db.rollback();
            return false;
        } else {
            qDebug() << __FILE__ << __LINE__ << "INSERT super admin" << __DATE__;
        }
        insert = "insert into alarm(name, create_by, create_time) values('weight','系统生成',datetime('now', 'localtime'))";
        if(!query.exec(insert)) {
            qDebug() << __FILE__ << __LINE__ << "Error: Fail to create table." << query.lastError() << insert;
            m_db.rollback();
            return false;
        } else {
            qDebug() << __FILE__ << __LINE__ << "INSERT super admin" << __DATE__;
        }
        insert = "insert into alarm(name, create_by, create_time) values('temperature','系统生成',datetime('now', 'localtime'))";
        if(!query.exec(insert)) {
            qDebug() << __FILE__ << __LINE__ << "Error: Fail to create table." << query.lastError() << insert;
            m_db.rollback();
            return false;
        } else {
            qDebug() << __FILE__ << __LINE__ << "INSERT super admin" << __DATE__;
        }
        insert = "insert into alarm(name, create_by, create_time) values('ph','系统生成',datetime('now', 'localtime'))";
        if(!query.exec(insert)) {
            qDebug() << __FILE__ << __LINE__ << "Error: Fail to create table." << query.lastError() << insert;
            m_db.rollback();
            return false;
        } else {
            qDebug() << __FILE__ << __LINE__ << "INSERT super admin" << __DATE__;
        }
        insert = "insert into alarm(name, create_by, create_time) values('conductivity','系统生成',datetime('now', 'localtime'))";
        if(!query.exec(insert)) {
            qDebug() << __FILE__ << __LINE__ << "Error: Fail to create table." << query.lastError() << insert;
            m_db.rollback();
            return false;
        } else {
            qDebug() << __FILE__ << __LINE__ << "INSERT super admin" << __DATE__;
        }
        m_db.commit();
        qDebug()<< "end:" << U::getThDt();
        query.clear();
    }else{
        qDebug() << __FILE__<< "alarm表已经存在" << U::getCompileDate();
        return true;
    }
    return true;
}

bool DB::initAlarmLog()
{
    if (!isTableExists("alarm_log")){
        QSqlQuery query(m_db);
        QString createSql = "CREATE TABLE IF NOT EXISTS alarm_log ("
                            "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                            "type TEXT ,"
                            "desc TEXT ,"
                            "value TEXT ,"
                            "origin TEXT ,"
                            "origin_status TEXT ,"
                            "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
                            "create_by TEXT"
                            ");";
        query.prepare(createSql);
        if(!query.exec()) {
            qDebug() << __FILE__ << __LINE__ << "Error: Fail to create table." << query.lastError();
            return false;
        } else {
            qDebug() << __FILE__ << __LINE__ << "Table created!" << __DATE__;
        }
        query.clear();
    }
    return true;
}

bool DB::initLog()
{
    if (!isTableExists("log")){
        QSqlQuery query(m_db);
        QString createSql = "CREATE TABLE IF NOT EXISTS log ("
                            "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                            "name TEXT,"
                            "real_name TEXT,"
                            "type TEXT,"
                            "desc TEXT,"
                            "key_info TEXT,"
                            "create_by TEXT,"
                            "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
                            ");";
        query.prepare(createSql);
        if(!query.exec()) {
            qDebug() << __FILE__ << __LINE__ << "Error: Fail to create table." << query.lastError();
            return false;
        } else {
            qDebug() << __FILE__ << __LINE__ << "Table created!" << __DATE__;
        }
        query.clear();
    }
    return true;
}

bool DB::initBatch()
{
    if (!isTableExists("batch")){
        QSqlQuery query(m_db);
        QString createSql = "CREATE TABLE IF NOT EXISTS batch ("
                            "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                            "name TEXT,"
                            "real_name TEXT,"
                            "product TEXT,"
                            "batch_no TEXT,"
                            "work_shop TEXT,"
                            "begin_time TIMESTAMP,"
                            "end_time TIMESTAMP,"
                            "desc TEXT,"
                            "create_by TEXT,"
                            "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
                            "UNIQUE (product,batch_no,work_shop)"
                            ");";
        query.prepare(createSql);
        if(!query.exec()) {
            qDebug() << __FILE__ << __LINE__ << "Error: Fail to create table." << query.lastError();
            return false;
        } else {
            qDebug() << __FILE__ << __LINE__ << "Table created!" << __DATE__;
        }
        query.clear();
    }
    return true;
}

bool DB::initBatchData()
{
    if (!isTableExists("batch_data")){
        QSqlQuery query(m_db);
        QString createSql = "CREATE TABLE IF NOT EXISTS batch_data ("
                            "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                            "name TEXT,"
                            "speed TEXT,"
                            "weight TEXT,"
                            "temperature TEXT,"
                            "ph TEXT,"
                            "conductivity TEXT,"
                            "desc TEXT,"
                            "create_by TEXT,"
                            "create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
                            ");";
        query.prepare(createSql);
        if(!query.exec()) {
            qDebug() << __FILE__ << __LINE__ << "Error: Fail to create table." << query.lastError();
            return false;
        } else {
            qDebug() << __FILE__ << __LINE__ << "Table created!" << __DATE__;
        }
        query.clear();
    }
    return true;
}

// 打开数据库，并初始化表
void DB::init()
{
    initDB();
    initParam();
    initAlarm();
    initAlarmLog();
    initUser();
    initLog();
    initBatch();
    initBatchData();
}

QHash<int, QByteArray> DB::getTableFieldRole(const QString &tableName)
{
    QHash<int, QByteArray> hs;
    QSqlQuery query(m_db);
    if (!query.exec(QString("SELECT * FROM %1 LIMIT 0").arg(tableName))) {
        qDebug() << "查询表结构失败：" << query.lastError().text();
        return hs;
    }

    // 获取查询结果的元数据（表结构）
    QSqlRecord record = query.record();

    int role = Qt::UserRole + 1;
    qDebug() << "表" << tableName << "的结构：";
    for (int i = 0; i < record.count(); ++i) {
        hs[role++]=record.fieldName(i).toUtf8();
    }
    return hs;
}

QString DB::getParamValue(const QString &paramName) const
{
    if (!m_db.isOpen()) m_db.isOpen();
    QSqlQuery query(m_db);
    QString sql = "SELECT * from param where name=:name";
    query.prepare(sql);
    query.bindValue(":name", paramName);
    if(!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << "query error: " << query.lastError();
    }
    else {
        qDebug() << __FILE__ << __LINE__ << "query success!";
    }

    QString s;
    while (query.next()) {
        s =  query.value("value").toString();
    }

    query.clear();
    return s;
}

bool DB::saveParamValue(const QString &paramName, const QString &value, const QString &update_by) const
{
    qDebug() << __FILE__ << __LINE__ << "param_value"<<paramName << ">"<<value;
    if (!m_db.isOpen()) m_db.isOpen();
    QSqlQuery query(m_db);
    QString sql = "update param set value=:value,update_time=datetime('now', 'localtime'),update_by=:update_by where name=:name";
    query.prepare(sql);
    query.bindValue(":name", paramName);
    query.bindValue(":value", value);
    query.bindValue(":update_by", update_by);
    if(!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << "query error: " << query.lastError();
        return false;
    }
    else {
        qDebug() << __FILE__ << __LINE__ << "query success!";
    }
    return true;
}

bool DB::insertParamDefault(const QString &paramName, const QString &value, const QString& desc) const
{
    if (!m_db.isOpen()) m_db.isOpen();
    QSqlQuery query(m_db);
    QString sql = "insert into param(name,value,status,desc,create_by) values(:name,:value,1,:desc,'系统初始化')";
    query.prepare(sql);
    query.bindValue(":name", paramName);
    query.bindValue(":value", value);
    query.bindValue(":desc", desc);
    if(!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << "query error: " << query.lastError();
        return false;
    }
    else {
        qDebug() << __FILE__ << __LINE__ << "query success!";
    }
    return true;
}

bool DB::insertAlaramLog(const QString &type, const QString &desc, const QString& value, const QString& origin, int status) const
{
    QSqlQuery query(m_db);

    // 显式写入创建时间为本地当前时间
    QString insert = "insert into alarm_log(type, desc, value, origin, origin_status, create_by,create_time) "
                     "values(:type,:desc,:value,:origin,:origin_status, 'SYS',datetime('now', 'localtime'))";
    query.prepare(insert);
    query.bindValue(":type", type);
    query.bindValue(":desc", desc);
    query.bindValue(":value", value);
    query.bindValue(":origin", origin);
    query.bindValue(":origin_status", status);

    if(!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << U::getThDt() << query.lastError() << insert;
    } else {
        qDebug() << __FILE__ << __LINE__ << "INSERT alaram_log" << type << desc << value << origin << status;
    }
    query.clear();
    return true;
}

QHash<QString, QVariant> DB::getSettingParams()
{
    QSqlQuery query(m_db);
    QString sql = "SELECT name,value from param where name like 'login_%' or name like 'system_%'";
    query.prepare(sql);
    if(!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << "query error: " << query.lastError();
    }
    else {
        qDebug() << __FILE__ << __LINE__ << "query success!";
    }


    QHash<QString, QVariant> hs;
    while (query.next()) {
        hs[query.value("name").toString()] = query.value("value");
    }
    query.clear();
    return hs;
}

bool DB::getAlarmParams(QHash<QString, QVariant> &speed, QHash<QString, QVariant> &temperature,QHash<QString, QVariant> &weight,QHash<QString, QVariant> &ph,QHash<QString, QVariant> &conductivity)
{
    QSqlQuery query(m_db);
    QString sql = "SELECT * from alarm";
    query.prepare(sql);
    if(!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << "query error: " << query.lastError();
    }
    else {
        qDebug() << __FILE__ << __LINE__ << "query success!";
    }

    while (query.next()) {
        QString name = query.value("name").toString();
        // qDebug() << __FILE__ << __LINE__ << "初始化alarm参数中>>>>>>>>>>>>>>>>>>>>>"<< name;

        if (name.compare("speed") == 0){
            speed["ll"] = query.value("ll").toString();
            speed["l"] = query.value("l").toString();
            speed["h"] = query.value("h").toString();
            speed["hh"] = query.value("hh").toString();

            speed["llStatus"] = query.value("llStatus");
            speed["lStatus"] = query.value("lStatus");
            speed["hStatus"] = query.value("hStatus");
            speed["hhStatus"] = query.value("hhStatus");
        }

        if (name.compare("temperature") == 0){
            temperature["ll"] = query.value("ll").toString();
            temperature["l"] = query.value("l").toString();
            temperature["h"] = query.value("h").toString();
            temperature["hh"] = query.value("hh").toString();

            temperature["llStatus"] = query.value("llStatus");
            temperature["lStatus"] = query.value("lStatus");
            temperature["hStatus"] = query.value("hStatus");
            temperature["hhStatus"] = query.value("hhStatus");
        }

        if (name.compare("weight") == 0){
            weight["ll"] = query.value("ll").toString();
            weight["l"] = query.value("l").toString();
            weight["h"] = query.value("h").toString();
            weight["hh"] = query.value("hh").toString();

            weight["llStatus"] = query.value("llStatus");
            weight["lStatus"] = query.value("lStatus");
            weight["hStatus"] = query.value("hStatus");
            weight["hhStatus"] = query.value("hhStatus");
        }

        if (name.compare("ph") == 0){
            ph["ll"] = query.value("ll").toString();
            ph["l"] = query.value("l").toString();
            ph["h"] = query.value("h").toString();
            ph["hh"] = query.value("hh").toString();

            ph["llStatus"] = query.value("llStatus");
            ph["lStatus"] = query.value("lStatus");
            ph["hStatus"] = query.value("hStatus");
            ph["hhStatus"] = query.value("hhStatus");
        }

        if (name.compare("conductivity") == 0){
            conductivity["ll"] = query.value("ll").toString();
            conductivity["l"] = query.value("l").toString();
            conductivity["h"] = query.value("h").toString();
            conductivity["hh"] = query.value("hh").toString();

            conductivity["llStatus"] = query.value("llStatus");
            conductivity["lStatus"] = query.value("lStatus");
            conductivity["hStatus"] = query.value("hStatus");
            conductivity["hhStatus"] = query.value("hhStatus");
        }
    }
    query.clear();
    return true;
}

QString DB::getAlarmParam(const QString &param)
{
    return getSettingParam(param);
}

bool DB::saveAlarmAll(QHash<QString, QVariant> speed, QHash<QString, QVariant> weight, QHash<QString, QVariant> temperature
                      , QHash<QString, QVariant> ph, QHash<QString, QVariant> conductivity,const QString &update_by)
{

    if (!m_db.isOpen())m_db.open();
    if (!m_db.transaction()) {
        qCritical() << "开启事务失败：" << m_db.lastError().text();
        return false;
    }
    saveAlarmParam(speed, update_by, "speed");
    saveAlarmParam(weight, update_by, "weight");
    saveAlarmParam(temperature, update_by, "temperature");
    saveAlarmParam(ph, update_by, "ph");
    saveAlarmParam(conductivity, update_by, "conductivity");

    m_db.commit();
    qDebug()<< "end:" << U::getThDt();
    return true;
}

bool DB::saveAlarmParam(QHash<QString, QVariant> param, const QString &update_by, const QString& name)
{
    QSqlQuery query(m_db);

    QString updateSql = "UPDATE alarm SET ll=:ll, llStatus=:llStatus, l=:l, lStatus=:lStatus,h=:h, hStatus=:hStatus, hh=:hh, hhStatus=:hhStatus, update_by=:update_by, update_time=datetime('now', 'localtime') WHERE name=:name";
    query.prepare(updateSql);

    qDebug() <<">>>>>"<<name<<param["ll"].toString()<< param["l"].toString()<<param["h"].toString()<<param["hh"].toString() << "checked:"
        <<param["llStatus"].toInt()<< param["lStatus"].toInt()<<param["hStatus"].toInt()<<param["hhStatus"].toInt();
    query.bindValue(":ll", param["ll"].toString());
    query.bindValue(":l", param["l"].toString());
    query.bindValue(":h", param["h"].toString());
    query.bindValue(":hh", param["hh"].toString());

    query.bindValue(":llStatus", param["llStatus"].toInt());
    query.bindValue(":lStatus", param["lStatus"].toInt());
    query.bindValue(":hStatus", param["hStatus"].toInt());
    query.bindValue(":hhStatus", param["hhStatus"].toInt());

    query.bindValue(":update_by", update_by);
    query.bindValue(":name", name);

    if(!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << ">>update error:" << query.lastError() << updateSql;
        m_db.rollback();
        return false;
    } else {
        qDebug() << __FILE__ << __LINE__ << "INSERT super admin" << __DATE__;
    }
    return true;
}

QString DB::getSettingParam(const QString &param)
{
    QSqlQuery query(m_db);
    QString sql = "SELECT name,value from param where name =:param";
    query.prepare(sql);
    query.bindValue(":param", param);
    if(!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << "query error: " << query.lastError();
    }
    else {
        qDebug() << __FILE__ << __LINE__ << "query success!";
    }

    QString re = "";
    if (query.next()) {
        re = query.value("value").toString();
        qDebug() << __FILE__ << __LINE__ << "query success!";
    }
    query.clear();
    return re;
}

bool DB::addUser(const QString &name, const QString password, const QString &real_name, const QString &expiration, const QString &status, const QString &permission, const QString &create_by)
{
    QSqlQuery query(m_db);

    // 显式写入创建时间为本地当前时间
    QString insertUser = "insert into user(name,password,real_name,expiration,status,permission,create_by,create_time) values(:name,:password,:real_name,:expiration,:status,:permission,:create_by,datetime('now', 'localtime'))";
    query.prepare(insertUser);
    query.bindValue(":name", name);
    query.bindValue(":password", password);
    query.bindValue(":real_name", real_name);
    query.bindValue(":expiration", expiration);
    query.bindValue(":status", status);

    query.bindValue(":permission", permission);
    query.bindValue(":create_by", create_by);
    // query.bindValue(":create_time", QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss"));

    if(!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << "Error: Fail to create table." << query.lastError() << insertUser;
    } else {
        qDebug() << __FILE__ << __LINE__ << "INSERT super admin" << __DATE__;
    }
    query.clear();
    return true;
}

bool DB::addBatch(const QString &name, const QString realName, QString batchNo,
                  QString product, QString workShop, QString beginTime,
                  QString createBy)
{
    QSqlQuery query(m_db);

    // 显式写入创建时间为本地当前时间
    QString insert = "insert into batch(name, real_name, product, work_shop, batch_no,begin_time, create_by,create_time) "
                         "values(:name,:real_name,:product,:work_shop,:batch_no,:begin_time, :create_by,datetime('now', 'localtime'))";
    query.prepare(insert);
    query.bindValue(":name", name);
    query.bindValue(":real_name", realName);
    query.bindValue(":batch_no", batchNo);
    query.bindValue(":product", product);
    query.bindValue(":work_shop", workShop);

    query.bindValue(":begin_time", beginTime);
    query.bindValue(":create_by", createBy);

    if(!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << "Error: Fail to create table." << query.lastError() << insert;
    } else {
        qDebug() << __FILE__ << __LINE__ << "INSERT super admin" << __DATE__;
    }
    query.clear();
    return true;
}

bool DB::updateBatch(const QString desc, QString endTime, QString batchNo, QString product, QString workShop)
{
    QSqlQuery query(m_db);
    QString sql = "update batch set desc=:desc, end_time=:end_time where batch_no=:batch_no and product=:product and work_shop=:work_shop";
    query.prepare(sql);
    query.bindValue(":desc", desc);
    query.bindValue(":end_time", endTime);
    query.bindValue(":batch_no", batchNo);
    query.bindValue(":product", product);
    query.bindValue(":work_shop", workShop);

    if(!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << "query error: " << query.lastError();
        return false;
    }
    else {
        qDebug() << __FILE__ << __LINE__ << "query success!";
    }
    return true;
}

bool DB::updateUser(const QString &id, const QString &name, const QString password, const QString &real_name, const QString &expiration, const QString &status, const QString &permission, const QString &update_by)
{
    QSqlQuery query(m_db);
    QString updateSql = "UPDATE user SET name=:name, password=:password, real_name=:real_name, expiration=:expiration, status=:status, permission=:permission, update_by=:update_by, update_time=datetime('now', 'localtime') WHERE id=:id";
    query.prepare(updateSql);
    query.bindValue(":name", name);
    query.bindValue(":password", password);
    query.bindValue(":real_name", real_name);
    query.bindValue(":expiration", expiration);
    query.bindValue(":status", status);
    query.bindValue(":permission", permission);
    query.bindValue(":update_by", update_by);
    // query.bindValue(":update_time", QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss"));
    query.bindValue(":id", id);

    if (!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << "update error:" << query.lastError() << updateSql;
        qDebug() <<"id"<<id<<"name:"<<name;
        return false;
    }
    return true;
}

bool DB::modifyPassword(const QString& id, const QString& password)
{
    QSqlQuery query(m_db);
    QString updateSql = "UPDATE user SET password=:password WHERE id=:id";
    query.prepare(updateSql);
    query.bindValue(":password", password);
    query.bindValue(":id", id);

    if (!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << "update error:" << query.lastError() << updateSql;
        qDebug() <<"id"<<id<<"name:"<<password;
        return false;
    }
    return true;
}

bool DB::addLog(const QString &name,const QString& realName, const QString &type, const QString &desc, const QString keyInfo, const QString &create_by)
{
    QSqlQuery query(m_db);

    // 显式写入创建时间为本地当前时间
    QString insertLog = "insert into log(name,real_name,type,desc,key_info,create_by,create_time) values(:name, :real_name, :type,:desc,:key_info,:create_by, datetime('now', 'localtime'))";
    query.prepare(insertLog);
    query.bindValue(":name", name);
    query.bindValue(":real_name", realName);
    query.bindValue(":type", type);
    query.bindValue(":desc", desc);
    query.bindValue(":key_info", keyInfo);
    query.bindValue(":create_by", create_by);

    if(!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << "Error: Fail to create table." << query.lastError() << insertLog;
    } else {
        qDebug() << __FILE__ << __LINE__ << "add log" << __DATE__;
    }
    query.clear();
    return true;
}

int DB::isExist(const QString &name,const QString& type, const QString &desc)
{
    QSqlQuery query(m_db);
    // 查询sqlite_master系统表，检查是否存在指定表
    // type='table' 表示只检查表，不包括视图等
    // name=? 是参数化查询，避免SQL注入和表名包含特殊字符的问题
    QString sql = "SELECT count(*) as c FROM log WHERE type=:type AND name=:name and desc = :desc";
    query.prepare(sql);
    query.bindValue(":name", name);
    query.bindValue(":type", type);
    query.bindValue(":desc", desc);

    if (!query.exec()) {
        qWarning() << "查询表是否存在失败：" << query.lastError().text();
    }

    int r = 0;
    if(query.next()){
        r = query.value("c").toInt();
    }
    qDebug() << "query:"<<type<<" name:"<< name << " key_info:"<<desc;
    query.clear();
    return r;
}

bool DB::hasBeenUsed(int count, const QString &password, const QString &type, const QString &log_to, const QString &key_info)
{
    QSqlQuery query(m_db);
    // 查询sqlite_master系统表，检查是否存在指定表
    // type='table' 表示只检查表，不包括视图等
    // name=? 是参数化查询，避免SQL注入和表名包含特殊字符的问题
    QString sql = "SELECT  desc FROM log WHERE type=:type AND log_to=:log_to and key_info = :key_info order by create_time desc limit :num";
    query.prepare(sql);
    query.bindValue(":type", type);
    query.bindValue(":log_to", log_to);
    query.bindValue(":key_info", key_info);
    query.bindValue(":num", count);

    if (!query.exec()) {
        qWarning() << "查询表是否存在失败：" << query.lastError().text() << __FUNCTION__;
    }

    bool r = false;
    while(query.next()){
        qDebug() << query.value("desc").toString() << " pwd:" << password;
        if (query.value("desc").toString().compare(password) == 0) r = true;
    }
    qDebug() << "query:"<<type<<" log_to:"<< log_to << " key_info:"<<key_info << " r:" <<r;
    query.clear();
    return r;
}

bool DB::insertBatchData(QString name, QString speed, QString weight, QString temperature, QString ph, QString conductivity)
{
    QSqlQuery query(m_db);

    // 显式写入创建时间为本地当前时间
    QString insert = "insert into batch_data(name,speed,weight,temperature,ph,conductivity,create_by,create_time) values(:name,:speed,:weight,:temperature,:ph,:conductivity,'系统后台进程',datetime('now', 'localtime'))";
    query.prepare(insert);
    query.bindValue(":name", name);

    query.bindValue(":speed", speed);
    query.bindValue(":weight", weight);
    query.bindValue(":temperature", temperature);
    query.bindValue(":ph", ph);
    query.bindValue(":conductivity", conductivity);

    if(!query.exec()) {
        qDebug() << __FILE__ << __LINE__ << U::getThDt() << query.lastError() << insert;
    } else {
        qDebug() << __FILE__ << U::getThDt() << speed << ":" << weight <<":" << temperature <<":" << ph <<":" << conductivity;
    }
    query.clear();
    return true;
}

// 关闭数据库
void DB::close()
{
    if (m_db.isOpen()) {
        m_db.close();
        qDebug() << __FILE__ << __LINE__ << "database closed";
    }
}
